﻿using System;
using System.IO;
using System.Linq;
using System.Xml;
using CodeMilkerV2.Models;
using OfficeOpenXml;
using System.Collections.Generic;






namespace CodeMilkerV2.Repositories {
    public class EntityRepository {
        public Entity GetEntity(string filePath) {
            var entity = new Entity();
            var newFile = new FileInfo(filePath);


            using (var xlPackage = new ExcelPackage(newFile)) {
                // get the first worksheet in the workbook
                var worksheet = xlPackage.Workbook.Worksheets.FirstOrDefault();
                if (worksheet == null)
                    throw new Exception("No worksheet found");

                entity.EntityName = worksheet.Name.Trim();

                //the columns
                var properties = new string[]
                {
                    "Id",
                    "Name",
                    "Label",
                    "DataType",
                    "Length",
                    "Precision",
                    "Scale",
                    "IsNullable",
                    "DefaultValue",
                    "IsPKey",
                    "IsFKey",
                    "IsEdit",
                    "Required",
                    "Allow",
                    "MinVal",
                    "MaxVal",
                    "RegEx",
                    "Remark",
                };


                int iRow = 4;
                var attributes = new List<Models.Attribute>();
                var dataTypeRepository = new DataTypeRepository();
                var allowRepository = new AllowRepository();

                while (true) {
                    bool allColumnsAreEmpty = true;
                    for (var i = 2; i <= properties.Length; i++)
                        if (worksheet.Cells[iRow, i].Value != null && !String.IsNullOrEmpty(worksheet.Cells[iRow, i].Value.ToString())) {
                            allColumnsAreEmpty = false;
                            break;
                        }

                    if (allColumnsAreEmpty)
                        break;

                    int id = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "Id")].Value);
                    string name = worksheet.Cells[iRow, GetColumnIndex(properties, "Name")].Value as string;
                    string label = worksheet.Cells[iRow, GetColumnIndex(properties, "Label")].Value as string;
                    string dataType = worksheet.Cells[iRow, GetColumnIndex(properties, "DataType")].Value as string;
                    int length = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "Length")].Value);
                    int precision = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "Precision")].Value);
                    int scale = Convert.ToInt32(worksheet.Cells[iRow, GetColumnIndex(properties, "Scale")].Value);
                    bool isNullable = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsNullable")].Value);
                    string defaultValue = worksheet.Cells[iRow, GetColumnIndex(properties, "DefaultValue")].Value as string;
                    bool isPKey = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsPKey")].Value);
                    bool isFKey = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsFKey")].Value);
                    bool isEdit = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "IsEdit")].Value);
                    bool required = Convert.ToBoolean(worksheet.Cells[iRow, GetColumnIndex(properties, "Required")].Value);
                    string allow = worksheet.Cells[iRow, GetColumnIndex(properties, "Allow")].Value as string;
                    string minVal = Convert.ToString(worksheet.Cells[iRow, GetColumnIndex(properties, "MinVal")].Value);
                    string maxVal = Convert.ToString(worksheet.Cells[iRow, GetColumnIndex(properties, "MaxVal")].Value);
                    string regEx = worksheet.Cells[iRow, GetColumnIndex(properties, "RegEx")].Value as string;
                    string remark = worksheet.Cells[iRow, GetColumnIndex(properties, "Remark")].Value as string;


                    attributes.Add(
                        new Models.Attribute {
                            Id = id,
                            Name = name,
                            Label = label,
                            DataTypeName = dataType,
                            DataType = dataTypeRepository.GetDataType(dataType),
                            Length = length,
                            Precision = precision,
                            Scale = scale,
                            IsNullable = isNullable,
                            DefaultValue = defaultValue,
                            IsPKey = isPKey,
                            IsFKey = isFKey,
                            IsEdit = isEdit,
                            Required = required,
                            AllowCondition = allow,
                            Allow = !String.IsNullOrEmpty(allow) ? allowRepository.GetAllow(allow) : null,
                            MinVal = minVal,
                            MaxVal = maxVal,
                            RegEx = regEx,
                            Remark = remark
                        });

                    // next row
                    iRow++;
                }

                if (attributes.Count > 0)
                    entity.Attributes = attributes;


            }//end using

            return entity;
        }

        protected virtual int GetColumnIndex(string[] properties, string columnName) {
            if (properties == null)
                throw new ArgumentNullException("properties");

            if (columnName == null)
                throw new ArgumentNullException("columnName");

            for (int i = 0; i < properties.Length; i++)
                if (properties[i].Equals(columnName, StringComparison.InvariantCultureIgnoreCase))
                    return i + 1; //excel indexes start from 1
            return 0;
        }
    }


}

